package com.reverse.engineering.util.core.run.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.ApplicationProperties;
import com.reverse.engineering.util.core.run.bean.database.Database;
import com.reverse.engineering.util.core.run.bean.database.Field;
import com.reverse.engineering.util.core.run.bean.database.Table;
import com.reverse.engineering.util.core.run.enums.FieldEquals;
import com.reverse.engineering.util.core.run.util.interfaces.DatabaseUtilsInterface;
import com.reverse.engineering.util.core.run.util.interfaces.impl.DatabaseUtilsImpl;

/**
 * Database工具类
 * 
 * @author liu
 * @Date 2020-06-15 14:45:59
 * @Description
 *
 */
public class DatabaseUtils {

    private static final DatabaseUtilsInterface dbUtilsInterface = DatabaseUtilsImpl.getInstances();

    /**
     * 执行Sql插入语句
     * 
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    public static int insert(String sql, Object obj[]) throws SQLException {
        return dbUtilsInterface.insert(sql, obj);
    }

    /**
     * 执行Sql插入语句(长连接)
     * 
     * @param conn 连接对象
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    public static int insert(Object Object, String sql, Object obj[]) throws SQLException {
        return dbUtilsInterface.insert(Object, sql, obj);
    }

    /**
     * 执行Sql更新语句
     * 
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    public static int executeUpdate(String sql, Object obj[]) throws SQLException {
        return dbUtilsInterface.executeUpdate(sql, obj);
    }

    /**
     * 执行Sql更新语句(长连接)
     * 
     * @param conn 连接对象
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    public static int executeUpdate(Object conn, String sql, Object obj[]) throws SQLException {
        return dbUtilsInterface.executeUpdate(conn, sql, obj);
    }

    /**
     * 执行Sql查询语句
     * 
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    public static List<HashMap<String, Object>> executeQuery(String sql, Object obj[]) throws SQLException {
        return dbUtilsInterface.executeQuery(sql, obj);
    }

    /**
     * 执行Sql查询语句(长连接)
     * 
     * @param conn 连接对象
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    public static List<HashMap<String, Object>> executeQuery(Object conn, String sql, Object obj[])
        throws SQLException {
        return dbUtilsInterface.executeQuery(conn, sql, obj);
    }

    /**
     * 获取连接对象
     * 
     * @return
     * @throws SQLException
     */
    public static Object getConnection() throws SQLException {
        return dbUtilsInterface.getConnection();
    }

    /**
     * 获取连接对象
     * 
     * @param url 数据库路径
     * @param username 数据库帐号
     * @param password 数据库密码
     * @return
     * @throws SQLException
     */
    public static Object getConnection(String url, String username, String password) throws SQLException {
        return dbUtilsInterface.getConnection(url, username, password);
    }

    /**
     * 获取连接对象
     * 
     * @param url 数据库路径
     * @param port 端口号
     * @param username 数据库帐号
     * @param password 数据库密码
     * @return
     * @throws SQLException
     */
    public static Object getConnection(String url, int port, String username, String password) throws SQLException {
        return dbUtilsInterface.getConnection(url, port, username, password);
    }

    /**
     * 关闭连接(单个连接,顶级连接,比如:一级连接)
     * 
     * @param conn 要关闭的对象
     * @throws SQLException
     */
    public static void closeOne(Object conn) throws SQLException {
        dbUtilsInterface.utilsCloseOne(conn);
    }

    /**
     * 关闭连接(一组连接,比如:三级连接,二级连接,一级连接...)
     * 
     * @param conn 要关闭的对象集合
     * @throws SQLException
     */
    public static void close(Object... conn) throws SQLException {
        dbUtilsInterface.utilsClose(conn);
    }

    /**
     * 关闭连接(多组连接,每组除顶级外都是List,顶级是一个Object,比如:{三级连接...},{二级连接...},顶级连接)
     * 
     * @param conn 要关闭的对象集合
     * @throws SQLException
     */
    public static void closeList(Object... conn) throws SQLException {
        dbUtilsInterface.utilsCloseList(conn);
    }

    /**
     * 从数据库读取数据
     * @throws SQLException 
     */
    public static void readDatabase() throws SQLException {
        Connection conn = null;
        try {
            conn = (Connection)getConnection();
            getDatabase();
            getTable(conn);
            getField(conn);
            fieldSort();
            tableRemovePrefix();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 去除表的前缀和后缀
     */
    private static void tableRemovePrefix() {
        if (ApplicationProperties.readDatabasePrefix) {
            ArrayList<Table> tables = Database.tables;
            for (Table table : tables) {
                String tableName = table.getTableName();
                if (!"".equals(ApplicationProperties.databaseTablePrefix.trim())) {
                    if (tableName.startsWith(ApplicationProperties.databaseTablePrefix)) {
                        tableName = tableName.substring(tableName.indexOf(ApplicationProperties.databaseTablePrefix)
                            + ApplicationProperties.databaseTablePrefix.length(), tableName.length());
                    }
                }
                if (!"".equals(ApplicationProperties.databaseTableSuffix.trim())) {
                    if (tableName.endsWith(ApplicationProperties.databaseTableSuffix)) {
                        tableName = tableName.substring(
                            tableName.indexOf(0, tableName.lastIndexOf(ApplicationProperties.databaseTableSuffix)));
                    }
                }
                table.setTableName(tableName);
            }
        }
    }

    /**
     * 获取字段信息
     * 
     * @param conn
     * @throws SQLException
     */
    private static void getField(Connection conn) throws SQLException {
        String sql =
            "select COLUMN_NAME 字段名, column_comment 字段说明, column_type 字段类型, column_key 约束 from information_schema.columns where table_schema = '"
                + Database.databaseName + "' and table_name = '%s';";
        for (Table table : Database.tables) {
            ArrayList<Field> arrayList = new ArrayList<>();
            PreparedStatement prepareStatement = null;
            ResultSet executeQuery = null;
            try {
                prepareStatement = conn.prepareStatement(String.format(sql, table.getTableName()));
                executeQuery = prepareStatement.executeQuery();
                while (executeQuery.next()) {
                    Field field = new Field();
                    // ================================================================
                    field.setFieldName(executeQuery.getString(1));
                    field.setFieldComment(executeQuery.getString(2));
                    String fieldType = executeQuery.getString(3);
                    if (fieldType.lastIndexOf("(") > 0) {
                        fieldType = fieldType.substring(0, fieldType.lastIndexOf("("));
                    }
                    field.setFieldType(fieldType);
                    for (FieldEquals fieldEquals : FieldEquals.values()) {
                        if (field.getFieldType().equalsIgnoreCase(fieldEquals.getDatabase())) {
                            field.setFieldEquals(fieldEquals);
                        }
                    }
                    if (!table.getExistId() && ApplicationProperties.id.equalsIgnoreCase(field.getFieldName())) {
                        table.setExistId(true);
                        table.setIdName(field.getFieldName());
                    }
                    if ("PRI".equalsIgnoreCase(executeQuery.getString(4))) {
                        table.setExistId(true);
                        table.setIdName(field.getFieldName());
                    }
                    if (ApplicationProperties.serial_number.equalsIgnoreCase(field.getFieldName())) {
                        table.setExistSerialNumber(true);
                        table.setSerialNumberName(field.getFieldName());
                    }
                    if (ApplicationProperties.is_del.equalsIgnoreCase(field.getFieldName())) {
                        table.setExistDel(true);
                        table.setDelName(field.getFieldName());
                    }
                    arrayList.add(field);
                }
                table.setFields(arrayList);
            } finally {
                if (executeQuery != null) {
                    executeQuery.close();
                }
                if (prepareStatement != null) {
                    prepareStatement.close();
                }
            }
        }
    }

    /**
     * 获取表信息
     * 
     * @param conn
     * @throws SQLException
     */
    private static void getTable(Connection conn) throws SQLException {
        ArrayList<Table> arrayList = new ArrayList<>();
        // 获取表名
        String sql = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='"
            + Database.databaseName + "' ";
        if (ApplicationProperties.tables.length > 0) {
            StringBuilder tables = new StringBuilder();
            for (String tableName : ApplicationProperties.tables) {
                tables.append("'" + tableName + "'").append(",");
            }
            tables.replace(tables.lastIndexOf(","), tables.length(), "");
            sql += "AND table_name IN (" + tables.toString() + ")";
        }

        PreparedStatement prepareStatement = null;
        ResultSet executeQuery = null;
        try {
            prepareStatement = conn.prepareStatement(sql);
            executeQuery = prepareStatement.executeQuery();
            while (executeQuery.next()) {
                Table table = new Table();
                table.setTableName(executeQuery.getString(1));
                String comment = executeQuery.getString(2);
                table.setTableComment(StringUtils.isNotBlank(comment) ? comment : "");
                arrayList.add(table);
            }
        } finally {
            if (executeQuery != null) {
                executeQuery.close();
            }
            if (prepareStatement != null) {
                prepareStatement.close();
            }
        }
        Database.tables = arrayList;
    }

    /**
     * 获取数据库信息
     */
    private static void getDatabase() {
        Database.databaseName = ApplicationProperties.DATABASE_NAME;
    }

    /**
     * 对字段进行排序
     * 
     * @param arrayList
     * @return
     */
    private static void fieldSort() {
        ArrayList<Table> tables = Database.tables;
        for (Table table : tables) {
            ArrayList<Field> fields = table.getFields();
            ArrayList<Field> arrayList = new ArrayList<>();
            for (Field field : fields) {
                if (table.getExistId() && table.getIdName().equals(field.getFieldName())) {
                    arrayList.add(field);
                    break;
                }
            }
            for (Field field : fields) {
                if (table.getExistId() && table.getIdName().equals(field.getFieldName())) {
                    continue;
                }
                arrayList.add(field);
            }
            table.setFields(arrayList);
        }
    }

}